<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_klaim extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		WHERE tgl_klaim=DATE(NOW())';
		//echo $sql;
		return $sql;
	}

	function get_klaim($params) {
		$sql = 'SELECT kd_klaim, p.kd_peserta AS peserta,dokter,unit,dmk, 
		nama,kl.alamat as ALAMAT,tempat_lahir,tgl_lahir,tgl_klaim,tgl_masuk,tgl_keluar,no_rm,ppk,jenis_kunjungan,
		first_name,last_name,ket_feedback1,jk,DATEDIFF(tgl_keluar,tgl_masuk) AS lama,diagnosa_lain
		FROM klaim k
		LEFT JOIN rc_users u ON k.id_inputor=u.id
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk  ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk
		LEFT JOIN dokter d ON ppk.kd_ppk=d.kd_ppk
		LEFT JOIN unit un ON ppk.kd_ppk=un.kd_ppk
		WHERE kd_klaim=?';
		$query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_cari_nama($keyword='') {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik 
		WHERE nama like "%'.$keyword.'%" OR kd_klaim like "%'.$keyword.'%"
		ORDER BY nama,kd_klaim';
		return $sql;
	}

	function get_cari_ppk($keyword='') {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik 
		WHERE kd_ppk = '.$keyword.'
		ORDER BY nama,kd_klaim';
		return $sql;
	}

	function get_cari_tgl($keyword='',$awal='',$akhir='') {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik 
		WHERE kd_ppk = '.$keyword.' AND tgl_klaim BETWEEN "'.$awal.'" AND "'.$akhir.'"
		ORDER BY nama,kd_klaim';
		return $sql;
	}

	function get_cari_ppk_all() {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik 
		ORDER BY nama,kd_klaim';
		return $sql;
	}

	function get_cari_tgl_all($awal='',$akhir='') {
		$sql = 'SELECT kd_klaim, sj.kd_surat_jaminan AS KSJ, p.kd_peserta AS peserta, 
		nama, jenis_kunjungan,tgl_klaim  
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik 
		WHERE tgl_klaim BETWEEN "'.$awal.'" AND "'.$akhir.'"
		ORDER BY nama,kd_klaim';
		return $sql;
	}

	function get_peserta($kd_peserta) {
		$sql = 'SELECT kd_klaim, p.kd_peserta AS peserta, 
		nama,alamat,tempat_lahir,tgl_lahir,kd_ppk
		FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan mjk ON sj.id_jenis_kunjungan=mjk.id_jenis_kunjungan
		LEFT JOIN peserta p ON sj.kd_peserta=p.kd_peserta
		LEFT JOIN penduduk pd ON p.nik=pd.nik
		LEFT JOIN keluarga kl ON pd.kd_kk=kl.kd_kk
		WHERE kd_klaim=?';
		$query = $this->db->query($sql,$kd_peserta);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function cek($rule) {
		$sql = 'SELECT * FROM klaim WHERE agama=?';
		$query = $this->db->query($sql, $rule);
        return $query;
	}

	function cekUpdate($rule) {
		$sql = 'SELECT * FROM klaim WHERE agama=? AND id_agama<>?';
		$query = $this->db->query($sql, $rule);
        return $query;
	}

	function get_layanan($keyword) {
		$sql = 'SELECT kd_layanan,layanan,tarif FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN layanan l ON ppk.kd_ppk=l.kd_ppk
		WHERE k.kd_klaim=? AND id_kategori_layanan=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_layanan_kategori($keyword) {
		$sql = 'SELECT kl.id_kategori_layanan AS IDKL, kategori_layanan FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN layanan l ON ppk.kd_ppk=l.kd_ppk
		LEFT JOIN master_kategori_layanan kl ON l.id_kategori_layanan=kl.id_kategori_layanan
		WHERE k.kd_klaim=? GROUP BY IDKL';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_layanan_klaim($keyword) {
		$sql = 'SELECT kd_klaim,id_klaim_layanan,kl.kd_layanan,layanan,tarif_perda,tarif_diminta,
		frekuensi_diminta, kategori_layanan FROM klaim_layanan kl
		LEFT JOIN layanan l ON kl.kd_layanan=l.kd_layanan
		LEFT JOIN master_kategori_layanan mkl ON l.id_kategori_layanan=mkl.id_kategori_layanan
		WHERE kl.kd_klaim=? ORDER BY id_klaim_layanan';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_layanan_by($keyword) {
		$sql = 'SELECT * FROM layanan WHERE kd_layanan=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_layanan_klaim_by($keyword) {
		$sql = 'SELECT kategori_layanan,layanan,id_klaim_layanan,tarif_perda,tarif_diminta,frekuensi_diminta
		 FROM klaim_layanan kl 
		 LEFT JOIN layanan l ON kl.kd_layanan=l.kd_layanan
		 LEFT JOIN master_kategori_layanan mkl ON l.id_kategori_layanan=mkl.id_kategori_layanan
		 WHERE id_klaim_layanan=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export() {
		$sql = 'SELECT * FROM klaim';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function getPPK() {
		$sql = 'SELECT * FROM ppk WHERE id_jenis_ppk <> "1"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_diagnosa() {
		$sql = 'SELECT * FROM master_diagnosa WHERE not_klaim="T"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_penunjang() {
		$sql = 'SELECT * FROM master_kategori_penunjang';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_mpenunjang_by($keyword2) {
		$sql = 'SELECT * FROM master_kategori_penunjang WHERE id_kategori_penunjang=?';
		$query = $this->db->query($sql,$keyword2);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}


	function get_ket($params) {
		$sql = 'SELECT ket FROM klaim WHERE id_agama = ? AND id_produksi = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['ket'];
	}

	function save($kd_klaim, $dataUpdate) {
		$this->db->where('kd_klaim', $kd_klaim);
		$update = $this->db->update('klaim', $dataUpdate);
		return $update;
	}

	function cek_layanan($params){
		$sql="SELECT * FROM klaim_layanan WHERE kd_klaim=? AND kd_layanan=?";
		$query=$this->db->query($sql,$params);
		return $query;
	}

	function save_layanan($dataInsert) {
		$query = $this->db->insert('klaim_layanan', $dataInsert);
		return $query;
	}

	function get_by($params) {
		$sql = 'SELECT kd_klaim,no_rm,tgl_klaim,tgl_masuk,tgl_keluar,ket_feedback1,diagnosa_lain,dmk,
		klaim.kd_dokter AS KDDOK, klaim.kd_unit AS KDUNIT, DATEDIFF(tgl_keluar,tgl_masuk) AS lama_rawat 
		FROM klaim 
		LEFT JOIN surat_jaminan ON klaim.kd_surat_jaminan = surat_jaminan.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik 
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		WHERE kd_klaim = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}



	function delete($kd_klaim) {
		$this->db->where('kd_klaim', $kd_klaim);
		$delete = $this->db->delete('klaim');
		return $delete;
	}

	function update_layanan($id, $dataUpdate) {
		$this->db->where('id_klaim_layanan', $id);
		$update = $this->db->update('klaim_layanan', $dataUpdate);
		return $update;
	}

	function delete_layanan($id) {
		$this->db->where('id_klaim_layanan', $id);
		$delete = $this->db->delete('klaim_layanan');
		return $delete;
	}

	//diagnosa
	//get diagnosa klaim
	function get_diagnosa_klaim($keyword) {
		$sql = 'SELECT kd_klaim,id_klaim_diagnosa,kd.kd_diagnosa,diagnosa FROM klaim_diagnosa kd
		LEFT JOIN master_diagnosa md ON kd.kd_diagnosa=md.kd_diagnosa
		WHERE kd.kd_klaim=? ORDER BY id_klaim_diagnosa';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
	//save diagnosa
	function cek_diagnosa($params){
		$sql="SELECT * FROM klaim_diagnosa WHERE kd_klaim=? AND kd_diagnosa=?";
		$query=$this->db->query($sql,$params);
		return $query;
	}

	function save_diagnosa($dataInsert) {
		$query = $this->db->insert('klaim_diagnosa', $dataInsert);
		return $query;
	}
	//delete diagnosa
	function delete_diagnosa($id) {
		$this->db->where('id_klaim_diagnosa', $id);
		$delete = $this->db->delete('klaim_diagnosa');
		return $delete;
	}

	//penunjang
	//get penunjang klaim
	function get_penunjang_klaim($keyword) {
		$sql = 'SELECT kd_klaim,id_kategori_penunjang,id_klaim_penunjang,kp.kd_penunjang,penunjang,tarif_perda,tarif_diminta,frekuensi_diminta 
		FROM klaim_penunjang kp
		LEFT JOIN penunjang p ON kp.kd_penunjang=p.kd_penunjang
		WHERE kp.kd_klaim=? AND p.id_kategori_penunjang=? ORDER BY id_klaim_penunjang';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	//get penunjang
	function get_penunjang_pilihan($keyword) {
		$sql = 'SELECT kd_penunjang,penunjang,tarif FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN penunjang p ON ppk.kd_ppk=p.kd_ppk
		WHERE k.kd_klaim=? AND p.id_kategori_penunjang=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	//get penunjang by
	function get_penunjang_by($keyword) {
		$sql = 'SELECT * FROM penunjang WHERE kd_penunjang=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	//save penunjang
	function cek_penunjang($params){
		$sql="SELECT * FROM klaim_penunjang WHERE kd_klaim=? AND kd_penunjang=?";
		$query=$this->db->query($sql,$params);
		return $query;
	}

	function save_penunjang($dataInsert) {
		$query = $this->db->insert('klaim_penunjang', $dataInsert);
		return $query;
	}

	//hapus penunjang
	function delete_penunjang($id) {
		$this->db->where('id_klaim_penunjang', $id);
		$delete = $this->db->delete('klaim_penunjang');
		return $delete;
	}

	//edit penunjang
	function get_penunjang_klaim_by($keyword) {
		$sql = 'SELECT penunjang,id_klaim_penunjang,tarif_perda,tarif_diminta,frekuensi_diminta,id_kategori_penunjang
		 FROM klaim_penunjang kl 
		 LEFT JOIN penunjang l ON kl.kd_penunjang=l.kd_penunjang
		 WHERE id_klaim_penunjang=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	//update layanan
	function update_penunjang($id, $dataUpdate) {
		$this->db->where('id_klaim_penunjang', $id);
		$update = $this->db->update('klaim_penunjang', $dataUpdate);
		return $update;
	}

	//dokter dan unit
	function get_dokter($keyword) {
		$sql = 'SELECT d.kd_dokter KDDOK,dokter FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN dokter d ON ppk.kd_ppk=d.kd_ppk
		WHERE k.kd_klaim=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_unit($keyword) {
		$sql = 'SELECT u.kd_unit KDUNIT,unit FROM klaim k
		LEFT JOIN surat_jaminan sj ON k.kd_surat_jaminan=sj.kd_surat_jaminan
		LEFT JOIN ppk ON sj.kd_ppk=ppk.kd_ppk
		LEFT JOIN unit u ON ppk.kd_ppk=u.kd_ppk
		WHERE k.kd_klaim=?';
		$query = $this->db->query($sql, $keyword);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
}
